package com.wangpu.oa.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;

import com.wangpu.oa.util.DBUtil;

public abstract class AbstractTemplateDao<T,ID> implements TemplateDao<T, ID> {
	
	protected final String tableName;
	protected final String columns;
	
	public AbstractTemplateDao(String tableName,String columns) {
		this.tableName=tableName;
		this.columns=columns;
	}
	
	protected static String generateInsertSql(String tableName, String columns) {
		String[] columnArray = columns.split(",");
		StringBuilder sql = new StringBuilder();
		sql.append("insert into ").append(tableName).append("(");
		for (String column : columnArray) {
			sql.append(column).append(",");
		}
		sql.setCharAt(sql.length() - 1, ')');
		sql.append("values");
		sql.append("(");
		for (int i = columnArray.length; i-- > 0; sql.append("?").append(","))
			;
		sql.setCharAt(sql.length() - 1, ')');

		return sql.toString();
	}
	@Override
	public ID insert(T t) throws Exception {
		// 1创建连接
		Connection connection=DBUtil.getConnection();
		try {
			// 2创建语句对象
			
			String sql=generateInsertSql(tableName, "");
			PreparedStatement ps=connection.prepareStatement(sql);
			
			// 3执行语句获取结果
			ID id=this.entity2row(t, ps);
			
			ps.executeUpdate();
			connection.commit();
			return id;
			// 4关闭连接
		}finally {
			connection.close();
		}
	}

	@Override
	public void delete(ID id) throws Exception {
		// 1创建连接
		Connection connection=DBUtil.getConnection();
		try {
			// 2创建语句对象
			
			StringBuilder sql=new StringBuilder();
			sql.append("delete from ").append(tableName);
			sql.append("where id=?");
			PreparedStatement ps=connection.prepareStatement(sql.toString());
			
			// 3执行语句获取结果
			ps.setObject(1, id);
			ps.executeUpdate();
			connection.commit();
			
		}finally {
			// 4关闭连接
			connection.close();
		}
		
	}

	@Override
	public void update(ID id, Map<String, Object> map) throws Exception {
		if (map.isEmpty())
			return;
		// 1创建连接
		Connection connection = DBUtil.getConnection();
		try {

			// 2创建语句对象
			StringBuilder sql = new StringBuilder();
			sql.append("update ").append(tableName);
			sql.append(" set ");
			Object[] values = new Object[map.size()];
			int i = 0;
			for (Map.Entry<String, Object> entry : map.entrySet()) {
				sql.append(entry.getKey()).append("=?").append(",");
				values[i++] = entry.getValue();
			}
			sql.setCharAt(sql.length() - 1, ' ');
			sql.append("where id=?");

			PreparedStatement ps = connection.prepareStatement(sql.toString());

			// 3执行语句获取结果
			i = 1;
			for (Object value : values) {
				ps.setObject(i++, value);
			}
			ps.setObject(i++, id);
			ps.executeUpdate();
			connection.commit();
		} finally {
			// 4关闭连接
			connection.close();
		}
	}

	@Override
	public T select(String uniqueColumn, Object value) throws Exception {
		// 1创建连接
		Connection connection = DBUtil.getConnection();
		try {

			// 2创建语句对象
			StringBuilder sql = new StringBuilder();

			sql.append("select * from ").append(tableName).append(" where ").append(uniqueColumn).append("=?");
			PreparedStatement ps = connection.prepareStatement(sql.toString());

			// 3执行语句获取结果
			ps.setObject(1, value);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				// ORM
				return row2entity(rs);
			}
			// 4关闭连接
			return null;
		} finally {
			connection.close();
		}
	}

	@Override
	public boolean exists(String uniqueColumn, Object value) throws Exception {
		// 1创建连接
		Connection connection = DBUtil.getConnection();
		try {
			// 2创建语句对象
			StringBuilder sql = new StringBuilder();
			sql.append("select count(*) from ").append(tableName).append(" where ").append(uniqueColumn).append("=?");
			PreparedStatement ps = connection.prepareStatement(sql.toString());

			// 3执行语句获取结果
			ps.setObject(1, value);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				// ORM
				return rs.getInt(1) > 0;
			}
			// 4关闭连接
			return false;
		} finally {
			connection.close();
		}
	}
	
	//ORM
	protected abstract T row2entity(ResultSet rs) throws Exception;
	abstract protected ID entity2row(T t, PreparedStatement ps) throws Exception;
}
